# -*- coding: utf-8 -*-

import sqlite3

CREATE_KEY_TABLE = "CREATE TABLE IF NOT EXISTS key_point (id integer PRIMARY KEY autoincrement,key_word varchar(255),problem TEXT,author varchar(20),job_number varchar(20))"


class Sqlite3Helper:
    '操作sqlite3的方法类'

    def __init__(self):
        self.conn = sqlite3.connect("test.db")
        self.conn.text_factory = str
        self.c = self.conn.cursor()
        self.c.execute(CREATE_KEY_TABLE)

    def addKeyPoint(self,keyword,problem,author,jobnumber):
        INSERT = "INSERT into key_point (key_word,problem,author,job_number) values (?,?,?,?)"
        self.c.execute(INSERT,(keyword,problem,author,jobnumber))
        self.conn.commit()

    def deleteKeyPointById(self,id):
        DELETE = "delete from key_point where id = ?"
        r= self.c.execute(DELETE,id)
        print "delete count = " + r.rowcount
        self.conn.commit()
    def queryKeyPointByJobNumber(self,jobNumber):
        QUERY_BY_JOBNUM = "select * from key_point where job_number = ?"
        self.c.execute(QUERY_BY_JOBNUM,jobNumber)
        data = self.c.fetchall()
        return data

    def queryKeyPoint(self):
        QUERY = "select * from key_point"
        self.c.execute(QUERY)
        data = self.c.fetchall()
        return data

    def queryKeywords(self):
        QUERY = "select key_word from key_point"
        self.c.execute(QUERY)
        data = self.c.fetchall()
        keywords = []
        for item in data:
            for keyword in item:
                keywords.append(keyword)
        return keywords

